import os
import sqlite3

__author__ = 'gongqf'
print os.getcwd()
if os.path.exists(r'e:\web\flask\topshipping'):
    os.chdir(r'e:\web\flask\topshipping')
elif os.path.exists(r'l:\web\flask\topshipping'):
    os.chdir(r'l:\web\flask\topshipping')
elif os.path.exists(r'g:\web\flask\topshipping'):
    os.chdir(r'g:\web\flask\topshipping')


conn=sqlite3.connect('topshipping.db3')
conn.text_factory = str
countrys=conn.execute('select countryid,countryname from country').fetchall()
lines=conn.execute('select lineid,linename from line').fetchall()
#ports=conn.execute('select portid,portnameen from port').fetchall()
#for c in countrys:
#    print c[1]
#
#for l in lines:
#    print l[1]

def getline(ln):
    for line in lines:
        if ln==line[1]:
            print ln,line
            return line[0]

def getcountry(cn):
    for country in countrys:
        if cn==country[1]:
            print cn,country
            return country[0]

def getport(pne):
    for port in ports:
        if pne==port[1]:
            print pne,port
            return port[0]
ports=open(r'static\port.txt').readlines()
for port in ports:
    port=port.strip('\n')
    print port
    p=port.split('|')
    print p

    conn.execute('insert into port(port5code,portnameen,portname) values(?,?,?)',(p[0],p[1].upper(),p[2].upper()))
    portid=conn.execute('select last_insert_rowid()').fetchone()[0]
    conn.execute('insert into rcountryport(countryid,portid) values(?,?)',(getcountry(p[3]),portid))
    conn.execute('insert into rlineport(lineid,portid) values(?,?)',(getline(p[4]),portid))
conn.commit()